package com.welikemovies;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;



public class DatabaseHandler {
	
	/** Database info */
	private static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:ug";
	private static final String DB_USERNAME = "ora_z8d8";
	private static final String DB_PASSWORD = "a65186116";

	
	/** Names of the tables in the database */
	private static final String TABLE_WONAWARD = "wonaward";
	private static final String TABLE_STARSIN = "starsin";
	private static final String TABLE_COPY = "copy";
	private static final String TABLE_RESERVATION = "reservation";
	private static final String TABLE_ACTORS = "actors";
	private static final String TABLE_BRANCH = "branch";
	private static final String TABLE_MOVIE = "movie";
	private static final String TABLE_CUSTOMER = "customer";

	/** Attributes for table Movie */
	public static final String MOVIE_TITLE = "title";
	public static final String MOVIE_YEAR = "year";
	public static final String MOVIE_GENRE = "genre";
	
	/** Attributes for table Copy */
	private static final String COPY_LOCATION = "location";
	private static final String COPY_CITY = "city";
	private static final String COPY_STATUS = "status";
	private static final String COPY_ID = "copyID";
	private static final String COPY_TITLE = "title";
	
	/** Attributes for table Customer */
	private static final String CUSTOMER_LOGINID = "custID";
	private static final String CUSTOMER_NAME = "name";
	private static final String CUSTOMER_PHONE = "phone";
	private static final String CUSTOMER_ADDRESS = "address";
	private static final String CUSTOMER_PASSWORD = "password";
	
	/** Attributes for table Reservation */
	private static final String RESERVATION_ID = "rID";
	private static final String RESERVATION_FROMDATE = "fro";
	private static final String RESERVATION_TODATE = "too";
	private static final String RESERVATION_CUSTID = "custID";
	private static final String RESERVATION_COPYID = "copyID";
	
	/** Attributes for table Actors */
	private static final String ACTOR_ID = "actorID";
	private static final String ACTOR_NAME = "actorName";
	
	
	/**Attributes for table Branch */
	private static final String BRANCH_LOCATION = "location";
	
	/**Attributes for table wonAward*/
	private static final String AWARD_TYPE = "type";
	private static final String AWARD_NAME = "awardName";
	private static final String AWARD_YEAR = "awardYear";
	
	public int branchCount = 0;
	
	
	private Connection con;
	
	public void getDBConnection() {

		try {
			DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver() );
		
		} catch (SQLException e) {
			System.out.println("Error in connection!");
			e.printStackTrace();		
		}
		
		try {
			con = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
			
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}
	/**
	 * Inserts a movie into the database
	 * @param title The title of the movie
	 * @param year The year the movie was made
	 * @param genre The genre of the movie
	 * @throws SQLException
	 */
	public void insertMovie(String title, String year, String genre) throws SQLException {
		 
		PreparedStatement preparedStatement = null;
 
		String insertMovieSQL = "INSERT INTO " + TABLE_MOVIE 
				+ "(" + MOVIE_TITLE + ", " + MOVIE_YEAR + ", " + MOVIE_GENRE + ") VALUES"
				+ "(?,?,?)";
 
		try {
			preparedStatement = con.prepareStatement(insertMovieSQL);
 
			preparedStatement.setString(1, title);
			preparedStatement.setString(2, year);
			preparedStatement.setString(3, genre);
 
			// execute insert SQL statement
			preparedStatement.executeUpdate();
 
		} catch (SQLException e) {
			e.printStackTrace();
 
		} finally {
 
			if (preparedStatement != null) {
				preparedStatement.close();
			}

 
		}
 
	}
	
	public void insertCopy(String title, String year, String genre, String location, String city) throws SQLException {
		
		int copyID = 0;
		
		ResultSet rsMaxId = null;
		
		PreparedStatement preparedStatement = null;
		
		Statement statement = null;
 
		String addCopySQL = "INSERT INTO " + TABLE_COPY 
				+ "(" + COPY_ID + ", " + COPY_STATUS + ", " + COPY_LOCATION + ", " + COPY_CITY + ", "
				+ MOVIE_TITLE + ", " + MOVIE_YEAR + ") VALUES"	+ "(?,?,?,?,?,?)";
		
		String findMaxIDSQL = "SELECT MAX" + "(" + COPY_ID + ")" + " FROM " + TABLE_COPY;
				
		try {
			statement = con.createStatement();
			rsMaxId = statement.executeQuery(findMaxIDSQL);
			
		} catch (SQLException e) {
			System.out.println("Error in findMaxIDSQL!");
			e.printStackTrace();
		}
		 
		try {
			while(rsMaxId.next()) {
				copyID = rsMaxId.getInt(1) + 1;
			}
		} catch (SQLException e) {
			System.out.println("Error in findMaxIDSQL!");
			e.printStackTrace();
		}
		
		
		
		
		try {
			preparedStatement = con.prepareStatement(addCopySQL);
 
			preparedStatement.setInt(1, copyID);
			preparedStatement.setString(2, "TRUE");
			preparedStatement.setString(3, location);
			preparedStatement.setString(4, city);
			preparedStatement.setString(5, title);
			preparedStatement.setString(6, year);

 
			// execute insert SQL statement
			preparedStatement.executeUpdate();
 
		} catch (SQLException e) {
			e.printStackTrace();
 
		} finally {
 
			if (preparedStatement != null) {
				preparedStatement.close();
			}

 
		}

 
	}

	/**
	 * Delete a movie from the database
	 * @param title The title of the movie
	 * @param year The year the movie was made
	 * @throws SQLException
	 */
	public String deleteMovie(String title, String year) throws SQLException {

		Statement statement = null;
		Statement statement1 = null;
		
		ResultSet rs = null;
			
		String deleteMovieSQL = "Delete From Movie where title = '" + title + "' and year = '" + year + "'";
		
		String verifyReservedSQL = "SELECT " + COPY_TITLE + ", " + MOVIE_YEAR + " FROM " + TABLE_COPY + ", " 
				+ TABLE_RESERVATION + " WHERE reservation.copyID = copy.copyID ";
		
		try {
			statement1 = con.createStatement();
			rs = statement1.executeQuery(verifyReservedSQL);
			

		} catch (SQLException e) {
			System.out.println("error in verifyReservedSQL");
			e.printStackTrace();
		}
		
		while (rs.next()) {
			if (title.equals(rs.getString(1).trim()) && year.equals(rs.getString(2).trim())) {
				return "Cannot delete Movie - Reservation found.";
				
			} else {
		
				try {
			
					statement = con.createStatement();
					statement.executeUpdate(deleteMovieSQL);
					return "Successfully deleted " + title + "!";

				} catch (SQLException e) {
					System.out.println("error in deleteMovieSQL");
					e.printStackTrace();
 
				} finally {
 
					if (statement != null) {
						statement.close();
					}
				}
				
			} rs.close();
		}
		try {
			
			statement = con.createStatement();
			statement.executeUpdate(deleteMovieSQL);
			return "Successfully deleted " + title + "!";

		} catch (SQLException e) {
			System.out.println("error in deleteMovieSQL");
			e.printStackTrace();

		} finally {

			if (statement != null) {
				statement.close();
			}
		}
		return "Successfully deleted " + title + "!";
	}

	public void deleteCopy(int intcID ) throws SQLException {
		  
		Statement statement = null;
		String copyID = Integer.toString(intcID);
		 
		String deleteCopySQL = "DELETE FROM " + TABLE_COPY +" WHERE "+ 
		COPY_ID +" = " + copyID;
		 
		System.out.println(deleteCopySQL);
		 
		try {

			statement = con.createStatement();
			statement.executeQuery(deleteCopySQL);
			
		} catch (SQLException e) {
			System.out.println("Error in deleteCopySQL!");
			e.printStackTrace();

		} 

	}
	
	
	public ResultSet getMovie(String select, String title, String year, String genre, String actor) throws SQLException {
		 
		Statement statement = null;
		ResultSet rs = null;
		
		// Format the title the user entered.
		String result = new String("");
		if (!title.equals("")) {
			title = title.toLowerCase();
			String[] theTitle = title.split("\\s");
		    for (int x = 0; x < theTitle.length; x++) {
		    	if (!theTitle[x].equals("")) {
		         char[] stringArray = theTitle[x].toCharArray();
		         stringArray[0] = Character.toUpperCase(stringArray[0]);
		         result += new String(stringArray);
		         if (x != theTitle.length)
		        	 result += " ";
		    	}
		    }
		}
		else {
			result = title;
		}
		
		String getMovieSQL = "SELECT " + select + " FROM " + TABLE_MOVIE + " WHERE " + MOVIE_TITLE + " LIKE '%" + result + "%' AND " 
			+ MOVIE_YEAR + " LIKE '%" + year + "%' AND " + MOVIE_GENRE + " LIKE '%" + genre + "%'";
		
		try {

			statement = con.createStatement();
			rs = statement.executeQuery(getMovieSQL);
			return rs;

		} catch (SQLException e) {
			System.out.println("Error in getMovie!");
			e.printStackTrace();
		}
		return rs;	
 
	}
	
	public ResultSet getAllGenres() throws SQLException {
		 
		Statement statement = null;
		ResultSet rs = null;
		
		String getMovieSQL = "SELECT DISTINCT " + MOVIE_GENRE + " FROM " + TABLE_MOVIE + " ORDER BY " + MOVIE_GENRE;
		
		try {

			statement = con.createStatement();
			rs = statement.executeQuery(getMovieSQL);
			return rs;

		} catch (SQLException e) {
			System.out.println("Error in getAllGenre!");
			e.printStackTrace();
		}
		return rs;	
 
	}
	
	/**
	 * Gets the locations an number of available copies for the location which have the maximum number of available copies
	 * @param title The title of the movie
	 * @param year The year of the movie
	 * @return Returns location, city, and number of available copies at location
	 * @throws SQLException
	 */
	public ResultSet getAvailability(String title, String year) throws SQLException {
		 
		Statement statement = null;
		ResultSet rs = null;
		
		String getAvalibilitySQL = "SELECT " + COPY_LOCATION + ", " + COPY_CITY + ", COUNT(*) AS CNT FROM " 
				+ TABLE_COPY + " WHERE " + COPY_ID + " IN (SELECT " + COPY_ID + " FROM " + TABLE_COPY +
				" WHERE " + MOVIE_TITLE + " ='" +title +"' AND " + COPY_STATUS +"='TRUE' AND "+ MOVIE_YEAR + " = '" + year + "')"+ 
				" GROUP BY " + COPY_CITY +"," + COPY_LOCATION;
		
		String maxInt = "SELECT MAX(CNT) FROM (" + getAvalibilitySQL + ")";
		
		String getSQL = "SELECT " + COPY_LOCATION + ", " + COPY_CITY + ", CNT FROM (" + getAvalibilitySQL + ") WHERE CNT=(" + maxInt + ")";
		
		System.out.println(getSQL);
		
		try {

			statement = con.createStatement();
			rs = statement.executeQuery(getSQL);
			return rs;

		} catch (SQLException e) {
			System.out.println("Error in getAvalibility!");
			e.printStackTrace();
		}
		return rs;	
 
	}
	public ResultSet getAllReservations() throws SQLException {
		 
		Statement statement = null;
		ResultSet rs = null;
		
		String getAllReservationsySQL = "SELECT " + CUSTOMER_NAME + ", "+ COPY_TITLE +", " +
		 RESERVATION_TODATE +", " + RESERVATION_ID +", O." +COPY_ID+ " FROM " +TABLE_COPY+ " O, "
				+  TABLE_CUSTOMER + " C, " + TABLE_RESERVATION + " R WHERE R."+  
		 RESERVATION_CUSTID +" = C." + CUSTOMER_LOGINID + " AND R." + RESERVATION_COPYID +
		 " = O." + COPY_ID ;
		
		System.out.println(getAllReservationsySQL);
		
		try {

			statement = con.createStatement();
			rs = statement.executeQuery(getAllReservationsySQL);
			return rs;

		} catch (SQLException e) {
			System.out.println("Error in getAllReservationsySQL!");
			e.printStackTrace();

		}
		return rs;	
 
	}
	
	public ResultSet getActor(String title, String year) throws SQLException {
		 
		Statement statement = null;
		ResultSet rs = null;
		
		String getActorSQL = "SELECT A." + ACTOR_NAME +" FROM " +TABLE_ACTORS+ " A, "+
		 TABLE_STARSIN + " S WHERE A."+ ACTOR_ID +" = S." + ACTOR_ID +
		 " AND S." + MOVIE_TITLE + " = '" +title + "' AND S." + MOVIE_YEAR + " = '" +year + "'";
		
		System.out.println(getActorSQL);
		
		try {

			statement = con.createStatement();
			rs = statement.executeQuery(getActorSQL);
			return rs;

		} catch (SQLException e) {
			System.out.println("Error in getActorSQL!");
			e.printStackTrace();

		}
		return rs;	
 
	}
	public ResultSet getCustomerReservations(int intCustomerID) throws SQLException {
		 
		Statement statement = null;
		ResultSet rs = null;
		String customerID = Integer.toString(intCustomerID);
		
		String getCustomerReservationsySQL = "SELECT " + COPY_TITLE +", " +
		 RESERVATION_TODATE + ", " + COPY_LOCATION + " FROM " +TABLE_COPY+ " O, "+
		 TABLE_RESERVATION + " R WHERE R."+ RESERVATION_CUSTID +" = " +customerID +
		 " AND R." + RESERVATION_COPYID + " = O." + COPY_ID ;
		
		System.out.println(getCustomerReservationsySQL);
		
		try {

			statement = con.createStatement();
			rs = statement.executeQuery(getCustomerReservationsySQL);
			return rs;

		} catch (SQLException e) {
			System.out.println("Error in getCustomerReservationsySQL!");
			e.printStackTrace();

		}
		return rs;	
 
	}
	
	/**
	 * Inserts a user into the database
	 * @param loginID The login of the user
	 * @param password The password for validation of login
	 * @throws SQLException
	 */
	public String insertCustomer(String name, String phone, String address, String password) throws SQLException {
		
		String userID = null;
		
		ResultSet rsMaxUser = null;
		
		PreparedStatement preparedStatement = null;
		
		Statement statement = null;
 
		String addCustomerSQL = "INSERT INTO " + TABLE_CUSTOMER 
				+ "(" + CUSTOMER_LOGINID + ", " + CUSTOMER_NAME + ", " + CUSTOMER_PHONE + ", "
				+ CUSTOMER_ADDRESS + "," + CUSTOMER_PASSWORD + ") VALUES"
				+ "(?,?,?,?,?)";
		
		String findMaxIDSQL = "SELECT MAX(custID) FROM " + TABLE_CUSTOMER;
				
		try {
			statement = con.createStatement();
			rsMaxUser = statement.executeQuery(findMaxIDSQL);
			
		} catch (SQLException e) {
			System.out.println("Error in findMaxIDSQL!");
			e.printStackTrace();
		}
		 
		try {
			while(rsMaxUser.next()) {
				System.out.println("current max user is: " + rsMaxUser.getString(1) + "\n");
				int id = Integer.parseInt(rsMaxUser.getString(1)) + 1;
				userID = Integer.toString(id);
				System.out.println("new userId is: " + userID + "\n");
				
			}
		} catch (SQLException e) {
			
			System.out.println("Error in findMaxIDSQL!");
			e.printStackTrace();
		}
		
		rsMaxUser.close();
		
		try {
			preparedStatement = con.prepareStatement(addCustomerSQL);
 
			preparedStatement.setString(1, userID);
			preparedStatement.setString(2, name);
			preparedStatement.setString(3, phone);
			preparedStatement.setString(4, address);
			preparedStatement.setString(5, password);

 
			// execute insert SQL statement
			preparedStatement.executeUpdate();
 
		} catch (SQLException e) {
			e.printStackTrace();
 
		} finally {
 
			if (preparedStatement != null) {
				preparedStatement.close();
			}

 
		}

		return userID;
 
	}
	/**
	 * updates Customer information based on userID taken after login process.  All info is user-defined.
	 * @param userID
	 * @param name
	 * @param phone
	 * @param address
	 * @param password
	 * @throws SQLException
	 */
	public void updateCustomerInfo(String userID, String phone, String address, String password) throws SQLException {
		Statement statement = null;
		
		String updateCustomerSQL = "UPDATE " + TABLE_CUSTOMER + " SET " + CUSTOMER_PHONE + " = '" 
				+ phone + "', " + CUSTOMER_ADDRESS + " = '" + address + "', " + CUSTOMER_PASSWORD 
				+ " = '" + password + "' WHERE " + CUSTOMER_LOGINID + " = '" + userID + "'";
		
		try {
			statement = con.createStatement();
			statement.executeUpdate(updateCustomerSQL);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
		
		
	}
	
	/**
	 * Gives the smallest copyId from the given location, city which is available
	 * @param location
	 * @param city
	 * @return smallest copyId
	 */
	public int getMinCopyId(String location, String city, String title, String year) {
		Statement statement = null;
		ResultSet rs = null;
		int copyId = 0;
		
		String getMinCopyIdSQL = "SELECT MIN(" + COPY_ID + ") FROM " + TABLE_COPY + " WHERE " 
		+ COPY_LOCATION + " ='" + location + "' AND " + COPY_CITY + "='" + city + "' AND " + COPY_STATUS + "='TRUE' AND "
		+ MOVIE_TITLE + " ='" + title + "' AND " + MOVIE_YEAR + "='" + year + "'";
		
		System.out.println(getMinCopyIdSQL);
		
		try {
			statement = con.createStatement();
			rs = statement.executeQuery(getMinCopyIdSQL);
			
			while (rs.next()) {
				copyId = rs.getInt(1);
			}
			
	
		} catch (SQLException e) {
			System.out.println("Error in getMinCopyId!");
			e.printStackTrace();
		}
	
		return copyId;
	}
	
	/**
	 * finds user defined custID and associated password from customer table to verify identity
	 * @param LoginID
	 * @param enterPassword
	 * @return isCorrect (boolean)
	 */
	public boolean validateUser(String LoginID, char[] enterPassword) {
		
		Statement statement = null;
		ResultSet rs = null;
		String actualPass = null;
		
		String findPasswordSQL = "SELECT " + CUSTOMER_PASSWORD  + " FROM " + TABLE_CUSTOMER + " WHERE " + CUSTOMER_LOGINID + " = " 
				+ LoginID;
		
		boolean isCorrect = true;
		
		try {
			statement = con.createStatement();
			rs = statement.executeQuery(findPasswordSQL);
			

		} catch (SQLException e) {
			System.out.println("Error in findPassword!");
			e.printStackTrace();
		}
		
		try {
			while(rs.next()) {
				actualPass = rs.getString(1);
			}
		} catch (SQLException e) {
			System.out.println("Error in findPassword!");
			e.printStackTrace();
		}
		
		System.out.println("User entered: " + String.valueOf(enterPassword) + " and actual password is: " + actualPass);

		String enteredPassString = String.valueOf(enterPassword).trim();
		String foundPassString = String.valueOf(actualPass).trim();
		
		
		if (foundPassString.length() != enteredPassString.length()) {
			System.out.println("password length wrong\n");
			System.out.println("foundpasswordlength = " + foundPassString.length() + " entered password = " + enteredPassString.length());
			System.out.println("foundPassString = " + foundPassString + "." + "\n enteredPassString = " + enteredPassString + ".");
			isCorrect = false;
		}
		else if (foundPassString.equals(enteredPassString)) {
						
			System.out.println("password correct");
					
			isCorrect = true;  
		}
				
		else {
			System.out.println("password wrong");
			System.out.println("foundPassString = " + foundPassString +"." +"\n enteredPassString = " + enteredPassString + ".");
			isCorrect = false;
		}
		
		return isCorrect;
	}
	
	/**
	 * Inserts a user into the database
	 * @param loginID The login of the user
	 * @param password The password for validation of login
	 * @throws SQLException
	 */
	public void insertReservation(int custID, int copyID) throws SQLException {
		
		int ResID = 0;
		
		ResultSet rsMaxRes = null;
		
		PreparedStatement preparedStatement = null;
		
		Statement statement = null;
 
		String addReservationSQL = "INSERT INTO " + TABLE_RESERVATION 
				+ "(" + RESERVATION_ID + ", " + RESERVATION_FROMDATE + ", " + RESERVATION_TODATE + ", "
				+ RESERVATION_CUSTID + "," + RESERVATION_COPYID + ") VALUES"
				+ "(?,?,?,?,?)";
		
		String findMaxIDSQL = "SELECT MAX" + "(" + RESERVATION_ID + ")" + " FROM " + TABLE_RESERVATION;
				
		try {
			statement = con.createStatement();
			rsMaxRes = statement.executeQuery(findMaxIDSQL);
			
		} catch (SQLException e) {
			System.out.println("Error in findMaxIDSQL!");
			e.printStackTrace();
		}
		 
		try {
			while(rsMaxRes.next()) {
				ResID = rsMaxRes.getInt(1) + 1;
			}
		} catch (SQLException e) {
			System.out.println("Error in findMaxIDSQL!");
			e.printStackTrace();
		}
		
		
		
		try {
			preparedStatement = con.prepareStatement(addReservationSQL);
 
			preparedStatement.setInt(1, ResID);
			preparedStatement.setString(2, "4/2/2012");
			preparedStatement.setString(3, "4/4/2012");
			preparedStatement.setInt(4, custID);
			preparedStatement.setInt(5, copyID);

 
			// execute insert SQL statement
			preparedStatement.executeUpdate();
 
		} catch (SQLException e) {
			e.printStackTrace();
 
		} finally {
 
			if (preparedStatement != null) {
				preparedStatement.close();
			}

 
		}

 
	}

	
	
	public void deleteReservations(int intrID ) throws SQLException {
		  
		Statement statement = null;
		ResultSet rs = null;
		String rID = Integer.toString(intrID);
		 
		String deleteReservationsSQL = "DELETE FROM " + TABLE_RESERVATION +" WHERE "+ 
		RESERVATION_ID +" = " + rID;
		 
		System.out.println(deleteReservationsSQL);
		 
		try {

			statement = con.createStatement();
			rs = statement.executeQuery(deleteReservationsSQL);

		} catch (SQLException e) {
			System.out.println("Error in deleteReservationsSQL!");
			e.printStackTrace();

		} 

	}
	
	
	public void setCopyStatus(int copyID, String next_status) throws SQLException {
		  
		Statement statement = null;


		String setCopyStatusSQL = "UPDATE " + TABLE_COPY +" SET "+ COPY_STATUS + " = '"
		+next_status+ "' WHERE " + COPY_ID + " = " + copyID;
		 
		System.out.println(setCopyStatusSQL);

		try {
		   
			statement = con.createStatement();
			statement.executeUpdate(setCopyStatusSQL);

		} catch (SQLException e) {
			e.printStackTrace();
		 
		} finally {
		 
			if (statement != null) {
				statement.close();
			}
		 
		}
		  	 
	}
	
	public String customerStat() {
		ResultSet rs = null;
		Statement statement = null;
		String customerNumber = null;
		
		String countCustomersSQL = "SELECT COUNT (DISTINCT " + CUSTOMER_LOGINID + ")" + " FROM " + TABLE_CUSTOMER;
		
		try {
			statement = con.createStatement();
			rs = statement.executeQuery(countCustomersSQL);
			while (rs.next()) {
				customerNumber = rs.getString(1);
			}
			
		} catch (SQLException e) {
			System.out.println("Error in countCustomersSQL!");
			e.printStackTrace();
		}
		
		return customerNumber;
	}
	
	public String avgCopiesStat() {
		ResultSet rs = null;
		Statement statement = null;
		
		String avgCopiesNumber = null;
		
		String avgCopiesPerTitleSQL = "SELECT AVG(COPYCOUNT) " + "FROM (SELECT COUNT (*) AS COPYCOUNT FROM " 
				+ TABLE_COPY + " GROUP BY " + COPY_TITLE +")";
		
		try {
			statement = con.createStatement();
			rs = statement.executeQuery(avgCopiesPerTitleSQL);
			while (rs.next()) {
				System.out.println(rs.getInt(1));
				avgCopiesNumber = rs.getString(1);
			}
			
		} catch (SQLException e) {
			System.out.println("Error in avgCopiesSQL!");
			e.printStackTrace();
		}
		
		
		return avgCopiesNumber;
	}
	
	public String[] allMovieBranchStat() {
		
		ResultSet rs = null;
		ResultSet rs1 = null;
		
		Statement statement = null;
		Statement statement1 = null;
		
		
		
		int i = 0;
		
		String branchSQL = "SELECT " + BRANCH_LOCATION + " FROM " 
		+ TABLE_BRANCH + " WHERE NOT EXISTS (SELECT " + MOVIE_TITLE 
		+ " FROM " + TABLE_MOVIE + " WHERE " + MOVIE_TITLE 
		+ " NOT IN (SELECT " + COPY_TITLE + " FROM " + TABLE_COPY 
		+ " WHERE " + TABLE_BRANCH +"." + BRANCH_LOCATION + " = " 
		+ TABLE_COPY + "." + COPY_LOCATION + "))";
				
		try {
			
			statement = con.createStatement();
			rs = statement.executeQuery(branchSQL);
			
			while (rs.next()) {
				i++;
			}
			branchCount = i;
			
		} catch (SQLException e) {
			System.out.println("Error in branchSQL!");
			e.printStackTrace();
		}
		
		String[] branchList = new String[branchCount];
		
		try {
			
			statement1 = con.createStatement();
			rs1 = statement1.executeQuery(branchSQL);
			 int j=0;
				while (rs1.next()) {
					branchList[j] = rs1.getString(1).trim();
					j++;
				}
			
			} catch (SQLException e) {
				System.out.println("Error in branchSQL!");
				e.printStackTrace();
			}
				
	
		return branchList;
	}
	
	public ResultSet getAllLocations() throws SQLException {
		 
		Statement statement = null;
		ResultSet rs = null;
		
		String getAllLocationsSQL = "SELECT " + COPY_LOCATION + ", " + COPY_CITY + " FROM " + TABLE_BRANCH;
		
		System.out.println(getAllLocationsSQL);
		
		try {

			statement = con.createStatement();
			rs = statement.executeQuery(getAllLocationsSQL);
			return rs;

		} catch (SQLException e) {
			System.out.println("Error in getAllLocations!");
			e.printStackTrace();

		}
		return rs;	
 
	}

	public int getNumberOfCopies(String title, String year, String location, String city) throws SQLException {
		 
		Statement statement = null;
		ResultSet rs = null;
		int number = 0;
		
		String getAllLocationsSQL = "SELECT COUNT(*) FROM " + TABLE_COPY + " WHERE " + COPY_STATUS + "='TRUE' AND " 
			+ COPY_LOCATION + "='" + location + "' AND " + COPY_CITY + "='" + city + "' AND " 
			+ MOVIE_TITLE + "='" + title + "' AND " + MOVIE_YEAR+ "='" + year + "'";
		
		System.out.println(getAllLocationsSQL);
		
		try {

			statement = con.createStatement();
			rs = statement.executeQuery(getAllLocationsSQL);
			
			while (rs.next()) {
				number = rs.getInt(1);
			}
			

		} catch (SQLException e) {
			System.out.println("Error in getAllLocations!");
			e.printStackTrace();

		}
		return number;	
 
	}
	
	public ResultSet getAward(String title, String year) {;
	Statement statement = null;
	ResultSet rs = null;
	
	
	String awardSQL = "SELECT " + AWARD_TYPE + ", " + AWARD_NAME + ", " + AWARD_YEAR + " FROM " 
			+ TABLE_WONAWARD + " WHERE " + MOVIE_TITLE + " = '" + title + "' AND " + MOVIE_YEAR 
			+ " = '" + year + "'";
	
	try {
	
		statement = con.createStatement();
		rs = statement.executeQuery(awardSQL);
		System.out.println(awardSQL);
		return rs;
	
	} catch (SQLException e) {
		System.out.println("Error in awardSQL!");
		e.printStackTrace();
	
	}
	
	return rs;
	}
}




